package com.trytech.mongoocrawler.client.common.util;

import com.trytech.mongoocrawler.client.common.entity.WebResultEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * MySQL数据库工具类
 * @author coliza
 * @date 2017/03/21
 */
public class MySQLUtils {
    public static final String url = "jdbc:mysql://59.110.7.93:3306/crawler?zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2b8";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "jiangtao";
    public static final String password ="aliyunmysqljt";

    private static Connection conn = null;

    public static Connection getConnection() {
        try {
            if (conn == null) {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(url, user, password);//获取连接
            }
            return conn;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /******************
     *  查询
     * @param sql
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static List<WebResultEntity> queryList(String sql) throws ClassNotFoundException, SQLException {
        conn = getConnection();
        Statement satement = conn.prepareStatement(sql);
        ResultSet rs = satement.executeQuery(sql);
        List<WebResultEntity> list = new ArrayList<WebResultEntity>();
        while(rs.next()){
            String source = rs.getString(0);
            String html = rs.getString(1);
            String date = rs.getString(2);
            WebResultEntity webResultEntity = new WebResultEntity();
            webResultEntity.setSource(source);
            webResultEntity.setHtml(html);
            webResultEntity.setDate(date);
            list.add(webResultEntity);
        }
        return list;
    }
    /******************
     *  插入
     * @param sql
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean insert(String sql) throws SQLException {
        conn = getConnection();
        Statement satement = conn.prepareStatement(sql);
        return satement.execute(sql);
    }

    public static void close(){
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {

            }
        }
    }
}
